I am currently working on logical replication (master / slave) with PostgreSQL. It is working very well, but I would like to anticipate the scenario where the connexion is broken unexpectedly (master offline, or socket broken...).
I have found a way to mimic this scenario, and the following logs are produced :
2019-10-01 15:05:31.205 GMT [165] ERROR: could not receive data from WAL stream: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
2019-10-01 15:05:31.206 GMT [121] LOG: background worker "logical replication worker" (PID 165) exited with exit code 1
2019-10-01 15:05:31.207 GMT [170] LOG: logical replication apply worker for subscription "sub_2" has started
2019-10-01 15:05:31.207 GMT [170] ERROR: could not connect to the publisher: could not connect to server: Connection refused
Is the server running on host "100.x.x.x" and accepting
TCP/IP connections on port 5432?
Now, what I would like to do is find a way to disable the subscription automatically when it happens (ie. execute the following command), then the ERROR will not appear in the logs because the worker will not exist and will not try to connect to an offline master.
ALTER SUBSCRIPTION sub2 DISABLE
To do that, I see some options but I don't know how to apply them :
Catch the ERROR (either in WAL or "logical replication worker")
Find a way to execute some code at "logical replication worker" startup
Use a CRON (at application level, so not native to PSQL) to check if the server is offline (pulsation). If so, then execute the above command manually. Note that this solution may work but I would like to avoid it because there will still be some errors raised if the connexion is lost between two "ticks" of the CRON
Thank you very much for your help, cheers